#!/bin/env python
#-*-coding:utf-8-*-

import MySQLdb
import string
import sys 
reload(sys) 
sys.setdefaultencoding('utf8')
import ConfigParser

def get_item(data_dict,item):
    try:
       item_value = data_dict[item]
       return item_value
    except:
       pass


def get_parameters(conn):
    try:
        curs=conn.cursor()
        data=curs.execute('select name,value from v$parameter');
        data_list=curs.fetchall()
        parameters={}
        for item in data_list:
            parameters[item[0]] = item[1]

    except Exception,e:
        print e

    finally:
        curs.close()

    return parameters


def get_sysstat(conn):
    try:
        curs=conn.cursor()
        data=curs.execute('select name,value value from v$sysstat');
        data_list=curs.fetchall()
        sysstat={}
        for item in data_list:
            sysstat[item[0]] = item[1]

    except Exception,e:
        print e

    finally:
        curs.close()

    return sysstat


def get_instance(conn,field):
    try:
        curs=conn.cursor()
        curs.execute("select %s from v$instance" %(field) );
        result = curs.fetchone()[0]

    except Exception,e:
        result = ''
        print e

    finally:
        curs.close()

    return result


def get_database(conn,field):
    try:
        curs=conn.cursor()
        curs.execute("select %s from v$database" %(field) );
        result = curs.fetchone()[0]

    except Exception,e:
        result = ''
        print e

    finally:
        curs.close()

    return result


def get_version(conn):
    try:
        curs=conn.cursor()
        curs.execute("select product,version from product_component_version where product like '%Database%'");
        result = curs.fetchone()[1]

    except Exception,e:
        print e

    finally:
        curs.close()

    return result


def get_sessions(conn):
    try:
        curs=conn.cursor()
        curs.execute("Select a.SID,a.SERIAL#,a.STATUS,a.USERNAME,a.MACHINE,a.MODULE,a.EVENT,b.SQL_ID,b.SQL_TEXT from v$session a, v$sqlarea b where a.sql_hash_value = b.HASH_VALUE and  a.username not in('SYS','SYSTEM') and a.username is not null");
        return curs.fetchall()

    except Exception,e:
        return null    
        print e

    finally:
        curs.close()



def get_actives(conn):
    try:
        curs=conn.cursor()
        curs.execute("Select a.SID,a.SERIAL#,a.STATUS,a.USERNAME,a.MACHINE,a.MODULE,a.EVENT,b.SQL_ID,b.SQL_TEXT from v$session a, v$sqlarea b where a.sql_hash_value = b.HASH_VALUE and  a.username not in('SYS','SYSTEM') and a.username is not null and a.status='ACTIVE'");
        #curs.execute("select s.SID,s.SERIAL#,s.STATUS,s.USERNAME,s.MACHINE,s.MODULE,sw.EVENT,sw.WAIT_CLASS,sq.SQL_ID,sq.SQL_TEXT,s.BLOCKING_SESSION,sw.SECONDS_IN_WAIT,sw.STATE,s.TERMINAL,s.PROGRAM,s.OSUSER from v$session s,v$session_Wait sw, v$sqlarea sq where s.SID=sw.SID and s.status='ACTIVE' AND sw.WAIT_CLASS not in('Idle') and s.sql_hash_value = sq.HASH_VALUE  and s.username is not null and TERMINAL is not null");
        return curs.fetchall()

    except Exception,e:
        print e

    finally:
        curs.close()



def get_waits(conn):
    try:
        curs=conn.cursor()
        #curs.execute("Select a.SID,a.SERIAL#,a.STATUS,a.USERNAME,a.MACHINE,a.MODULE,a.EVENT,b.SQL_ID,b.SQL_TEXT from v$session a, v$sqlarea b where a.sql_hash_value = b.HASH_VALUE  and a.username is not null and  ( a.event like 'library%' or a.event like 'cursor%' or a.event like 'latch%'  or a.event like 'enq%' or a.event like 'log file%')");
        curs.execute("select s.SID,s.SERIAL#,s.STATUS,s.USERNAME,s.MACHINE,s.MODULE,sw.EVENT,sw.WAIT_CLASS,sq.SQL_ID,sq.SQL_TEXT,s.BLOCKING_SESSION,sw.SECONDS_IN_WAIT,sw.STATE,s.TERMINAL,s.PROGRAM,s.OSUSER from v$session s,v$session_Wait sw, v$sqlarea sq where s.SID=sw.SID and s.status='ACTIVE' AND sw.WAIT_CLASS not in('Idle') and s.sql_hash_value = sq.HASH_VALUE  and s.username is not null and TERMINAL is not null and  ( s.event like 'library%' or s.event like 'cursor%' or s.event like 'latch%'  or s.event like 'enq%' or s.event like 'log file%')");
        #curs.execute("select s.SID,s.SERIAL#,s.STATUS,s.USERNAME,s.MACHINE,s.MODULE,sw.EVENT,sw.WAIT_CLASS,sq.SQL_ID,sq.SQL_TEXT,s.BLOCKING_SESSION,sw.SECONDS_IN_WAIT,sw.STATE,s.TERMINAL,s.PROGRAM,s.OSUSER from v$session s,v$session_Wait sw, v$sqlarea sq where s.SID=sw.SID and s.status='ACTIVE' AND sw.WAIT_CLASS not in('Idle') and s.sql_hash_value = sq.HASH_VALUE  and s.username is not null and TERMINAL is not null ");
        return curs.fetchall()

    except Exception,e:
        return null
        print e

    finally:
        curs.close()

def get_abnormal_connections(conn,threshold_white_list):
    try:
       curs=conn.cursor()
       curs.execute("Select a.USERNAME,a.MACHINE,a.MODULE,a.EVENT,b.SQL_ID,b.SQL_TEXT from v$session a, v$sqlarea b where a.sql_hash_value = b.HASH_VALUE and  a.username not in('SYS','SYSTEM') and a.username is not null");
       result = curs.fetchall()
       if len(result)!=0:
          user_list = []
          for item in result:
             user_list.append(item[0])
             user_list.append(item[1])

          #print user_list 

    except Exception,e:
       return 0

    finally:
       curs.close()


def get_dg_stats(conn):
    try:
        curs=conn.cursor()
        curs.execute("SELECT substr((SUBSTR(VALUE,5)),0,2)*3600 + substr((SUBSTR(VALUE,5)),4,2)*60 + substr((SUBSTR(VALUE,5)),7,2) AS seconds,VALUE FROM v$dataguard_stats a WHERE NAME ='apply lag'");
        list = curs.fetchone()
        if list:
            result = 1
        else:
            result = 0
        return result

    except Exception,e:
        return null
        print e

    finally:
        curs.close()



def get_dg_delay(conn):
    try:
        curs=conn.cursor()
        curs.execute("SELECT substr((SUBSTR(VALUE,5)),0,2)*3600 + substr((SUBSTR(VALUE,5)),4,2)*60 + substr((SUBSTR(VALUE,5)),7,2) AS seconds,VALUE FROM v$dataguard_stats a WHERE NAME ='apply lag'");
        list = curs.fetchone()
        if list:
            result = list[0] 
        else:
            result = '---'
        return result

    except Exception,e:
        return null
        print e

    finally:
        curs.close()

def get_tablespace(conn):
    try:
        curs=conn.cursor()
        curs.execute("select df.tablespace_name ,totalspace total_size, (totalspace-freespace) used_size,freespace avail_size ,round((1-freespace/totalspace)*100)  as used_ratio from (select tablespace_name,round(sum(bytes)/1024/1024) totalspace from dba_data_files group by tablespace_name) df,(select tablespace_name,round(sum(bytes)/1024/1024) freespace from dba_free_space group by tablespace_name) fs where df.tablespace_name=fs.tablespace_name and df.tablespace_name not like 'UNDOTBS%'");
        list = curs.fetchall()
        return list

    except Exception,e:
        return null
        print e

    finally:
        curs.close()


def get_tablespace_new(conn):
    try:
        curs=conn.cursor()
        curs.execute("SELECT tablespace_name, sum_m + sum_cut , sum_free + sum_cut,to_char(100*((sum_m + sum_cut)-(sum_free+sum_cut))/(sum_m+sum_cut), '999.99') AS used_per, to_char(100*(sum_free+sum_cut)/(sum_m+sum_cut), '999.99')  AS free_per FROM ( SELECT tablespace_name,sum(bytes)/1024/1024 AS sum_m,sum(decode(autoextensible,'YES',maxbytes-bytes,0))/1024/1024 AS sum_cut FROM dba_data_files GROUP BY tablespace_name),( SELECT tablespace_name AS ts_name, sum(bytes/1024/1024) AS sum_free FROM dba_free_space GROUP BY tablespace_name ) WHERE tablespace_name = ts_name (+) Union SELECT tablespace_name, sum_m + sum_cut , sum_free + sum_cut,to_char(100*((sum_m + sum_cut)-(sum_free+sum_cut))/(sum_m+sum_cut), '999.99')  AS used_per, to_char(100*(sum_free+sum_cut)/(sum_m+sum_cut), '999.99')  AS free_per FROM (select tablespace_name, sum(decode(autoextensible,'YES',maxbytes,bytes))/1024/1024 AS sum_m,sum(decode(autoextensible,'YES',maxbytes-bytes,0))/1024/1024 AS sum_cut  from dba_temp_files group by tablespace_name),(select tablespace_name AS ts_name, sum(BYTES_FREE /1024/1024) AS sum_free from v$temp_space_header group by tablespace_name) Where tablespace_name = ts_name (+) order by free_per");
        list = curs.fetchall()
        return list

    except Exception,e:
        return null
        print e

    finally:
        curs.close()


def get_sqlmonitor(conn):
    try:
        curs=conn.cursor()
        curs.execute("select status,MODULE,username,sql_id,SQL_EXEC_START,LAST_REFRESH_TIME,ERROR_MESSAGE ,elapsed_time, cpu_time, fetches, buffer_gets, disk_reads,  direct_writes, application_wait_time,  concurrency_wait_time,  cluster_wait_time,  user_io_wait_time,plsql_exec_time,java_exec_time, SQL_TEXT from V$SQL_MONITOR where username !='SYS' and status not like 'DONE%'");
        list = curs.fetchall()
        return list

    except Exception,e:
        return null
        print e

    finally:
        curs.close()


def get_rollstat(conn):
    try:
        curs=conn.cursor()
        curs.execute("select * from v$transaction t, v$rollstat r, v$rollname u, v$session s where s.taddr = t.addr   and t.xidusn = r.usn   and r.usn = u.usn order by s.username");
        list = curs.fetchall()
        return list

    except Exception,e:
        return null
        print e

    finally:
        curs.close()


def get_temp_tbs(conn):
    try: 
        curs=conn.cursor()
        curs.execute("select se.username,se.sid,su.extents,su.blocks * to_number(rtrim(p.value)) as Space ,tablespace ,segtype,sql_text from v$sort_usage su,v$parameter p ,v$session se,v$sql s where p.name = 'db_block_size' and su.session_addr = se.saddr and s.hash_value = su.sqlhash and s.address = su.sqladdr order by se.username, se.sid");
        list = curs.fetchall()
        return list

    except Exception,e:
        return null
        print e

    finally:
        curs.close()


def get_table_hwm(conn):
    try:
        curs=conn.cursor()
        curs.execute("SELECT * FROM (SELECT OWNER,TABLE_NAME,NUM_ROWS,AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9 NEED_SPACE, BLOCKS*8/1024 TRUE_SPACE,(BLOCKS*8/1024-AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9) RECOVER_SPACE,(BLOCKS*8/1024-AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9)/(BLOCKS*8/1024)*100  RECOVER_RATE FROM dba_tables WHERE OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP','WMSYS','APPQOSSYS','XDB')) T WHERE RECOVER_SPACE > 100 order by recover_rate desc");
        list = curs.fetchall()
        return list

    except Exception,e:
        return null
        print e

    finally:
        curs.close()



def get_buffer_pool_rate(conn):
    try:
        curs=conn.cursor()
        curs.execute('SELECT physical_reads, db_block_gets, consistent_gets, NAME,100 * ( 1 - ( physical_reads / (consistent_gets + db_block_gets - physical_reads))) "Data Buffer Hit Ratio" FROM v$buffer_pool_statistics');
        list = curs.fetchone()
        return list

    except Exception,e:
        return null
        print e

    finally:
        curs.close()

def get_data_dict_rate(conn):
    try:
        curs=conn.cursor()
        curs.execute('select 1 - sum(getmisses) / sum(gets) "data dictionary hitratio" from v$rowcache');
        list = curs.fetchone()
        return list

    except Exception,e:
        return null
        print e

    finally:
        curs.close()


def get_indexes(conn):
    try:
        curs=conn.cursor()
        curs.execute("select owner,index_name,index_type,table_owner,table_name,status,distinct_keys,num_rows,blevel  From dba_indexes Where (status<>'VALID' or blevel>3 ) and distinct_keys is not null and num_rows is not null and blevel is not null and owner not in( 'SYSTEM','SYS')");
        return curs.fetchall()

    except Exception,e:
        return null
        print e

    finally:
        curs.close()

def format_indexes_table(data):
    table_header = "<table border=1 style='font-size:12px;color:#333333;border-width:1px;border-color:#666666;'>"
    content = "<tr><td>OWNER</td><td>INDEX_NAME</td><td>INDEX_TYPE</td><td>TABLE_OWNER</td><td>TABLE_NAME</td><td>STATUS</td><td>DISTINCT ROWS</td><td>NUM ROWS</td><td>BLEVEL</td></tr>"
    for item in data:
       content = content+"<tr><td>"+str(item[0])+"</td><td>"+str(item[1])+"</td><td>"+item[2]+"</td><td>"+item[3]+"</td><td>"+item[4]+"</td><td>"+item[5]+"</td><td>"+str(item[6])+"</td><td>"+str(item[7])+"</td><td>"+str(item[8])+"</td></tr>"
    table_footer = "</table>"
    table = table_header+content+table_footer
    return table

def get_objects(conn):
    try:
        curs=conn.cursor()
        curs.execute("SELECT owner, object_name, object_type,status FROM dba_objects WHERE status = 'INVALID' and object_type<> 'VIEW' and owner <> 'SYS'");
        return curs.fetchall()

    except Exception,e:
        return null
        print e

    finally:
        curs.close()

def format_objects_table(data):
    table_header = "<table border=1 style='font-size:12px;color:#333333;border-width:1px;border-color:#666666;'>"
    content = "<tr><td>OWNER</td><td>OBJECT_NAME</td><td>OBJECT_TYPE</td><td>STATUS</td></tr>"
    for item in data:
       content = content+"<tr><td>"+str(item[0])+"</td><td>"+str(item[1])+"</td><td>"+item[2]+"</td><td>"+item[3]+"</td></tr>"
    table_footer = "</table>"
    table = table_header+content+table_footer
    return table

def format_waits_table(data):
    table_header = "<table border=1 style='font-size:12px;color:#333333;border-width:1px;border-color:#666666;'>"
    content = "<tr><td>SID</td><td>SERIAL#</td><td>STATUS</td><td>USERNAME</td><td>MACHINE</td><td>MODULE</td><td>EVENT</td><td>WAIT_CLASS</td><td>SQL ID</td><td>SQL TEXT</td><td>BLOCKING_SESSION</td><td>SECONDS_IN_WAIT</td><td>STATE</td><td>TERMINAL</td><td>PROGRAM</td><td>OSUSER</td></tr>"
    for item in data:
       if item[10]=='' or item[10] is None:
          block_session = 'no';
       else:
          block_session = str(item[10]) 
       content = content+"<tr><td>"+str(item[0])+"</td><td>"+str(item[1])+"</td><td>"+item[2]+"</td><td>"+item[3]+"</td><td>"+item[4]+"</td><td>"+item[5]+"</td><td>"+item[6]+"</td><td>"+item[7]+"</td><td>"+item[8]+"</td><td>"+item[9]+"</td><td>"+block_session+"</td><td>"+str(item[11])+"</td><td>"+item[12]+"</td><td>"+item[13]+"</td><td>"+item[14]+"</td><td>"+item[15]+"</td></tr>"
    table_footer = "</table>"
    table = table_header+content+table_footer
    return table

def format_sessions_table(data):
    table_header = "<table border=1 style='font-size:12px;color:#333333;border-width:1px;border-color:#666666;'>"
    content = "<tr><td>SID</td><td>SERIAL#</td><td>STATUS</td><td>USERNAME</td><td>MACHINE</td><td>MODULE</td><td>EVENT</td><td>SQL ID</td><td>SQL TEXT</td></tr>"
    for item in data:
       content = content+"<tr><td>"+str(item[0])+"</td><td>"+str(item[1])+"</td><td>"+item[2]+"</td><td>"+item[3]+"</td><td>"+item[4]+"</td><td>"+item[5]+"</td><td>"+item[6]+"</td><td>"+item[7]+"</td><td>"+item[8]+"</td></tr>"
    table_footer = "</table>"
    table = table_header+content+table_footer
    return table

def format_sqlmonitor_table(data):
    table_header = "<table border=1 style='font-size:12px;color:#333333;border-width:1px;border-color:#666666;'>"
    content = "<tr><td>STATUS</td><td>MODULE</td><td>USERNAME</td><td>SQL_ID</td><td>SQL_EXEC_START</td><td>LAST_REFRESH_TIME</td><td>ERROR_MESSAGE</td><td>ELAPSED_TIME</td><td>CPU_TIME</td><td>FETCHES</td><td>BUFFER_GETS</td><td>DISK_READS</td><td>DIRECT_WRITES</td><td>APPLICATION_WAIT_TIME</td><td>CONCURRENCY_WAIT_TIME</td><td>CLUSTER_WAIT_TIME</td><td>USER_IO_WAIT_TIME</td><td>PLSQL_EXEC_TIME</td><td>JAVA_EXEC_TIME</td><td>SQL_TEXT</td></tr>"
    for item in data:
       if item[6]=='' or item[6] is None:
          ERROR_MESSAGE = 'no';
       else:
          ERROR_MESSAGE = item[6]
       content = content+"<tr><td>"+str(item[0])+"</td><td>"+str(item[1])+"</td><td>"+item[2]+"</td><td>"+item[3]+"</td><td>"+str(item[4])+"</td><td>"+str(item[5])+"</td><td>"+ERROR_MESSAGE+"</td><td>"+str(item[7])+"</td><td>"+str(item[8])+"</td><td>"+str(item[9])+"</td><td>"+str(item[10])+"</td><td>"+str(item[11])+"</td><td>"+str(item[12])+"</td><td>"+str(item[13])+"</td><td>"+str(item[14])+"</td><td>"+str(item[15])+"</td><td>"+str(item[16])+"</td><td>"+str(item[17])+"</td><td>"+str(item[18])+"</td><td>"+item[19]+"</td></tr>"
    table_footer = "</table>"
    table = table_header+content+table_footer
    return table


def format_rollstat_table(data):
    table_header = "<table border=1 style='font-size:12px;color:#333333;border-width:1px;border-color:#666666;'>"
    content = "<tr><td>XUNSID</td><td>XIDSLOT</td><td>XIDSQN</td><td>STATUS</td><td>START_TIME</td><td>LOG_IO</td><td>PHY_IO</td><td>CR_GET</td><td>CR_CHANGE</td><td>NAME</td><td>SID</td><td>SERIAL#</td><td>USERNAME</td></tr>"
    for item in data:
       content = content+"<tr><td>"+str(item[1])+"</td><td>"+str(item[2])+"</td><td>"+str(item[3])+"</td><td>"+str(item[8])+"</td><td>"+str(item[9])+"</td><td>"+str(item[34])+"</td><td>"+str(item[35])+"</td><td>"+str(item[36])+"</td><td>"+str(item[37])+"</td><td>"+str(item[65])+"</td><td>"+str(item[67])+"</td><td>"+str(item[68])+"</td><td>"+str(item[72])+"</td></tr>"
    table_footer = "</table>"
    table = table_header+content+table_footer
    return table


def format_temp_tbs_table(data):
    table_header = "<table border=1 style='font-size:12px;color:#333333;border-width:1px;border-color:#666666;'>"
    content = "<tr><td>USERNAME</td><td>SID</td><td>EXTENTS</td><td>SPACE</td><td>TABLESPACE</td><td>SEGTYPE</td><td>SQL TEXT</td></tr>"
    for item in data:
       content = content+"<tr><td>"+item[0]+"</td><td>"+str(item[1])+"</td><td>"+str(item[2])+"</td><td>"+str(item[3])+"</td><td>"+item[4]+"</td><td>"+item[5]+"</td><td>"+item[6]+"</td></tr>"
    table_footer = "</table>"
    table = table_header+content+table_footer
    return table


def format_table_hwm_table(data):
    table_header = "<table border=1 style='font-size:12px;color:#333333;border-width:1px;border-color:#666666;'>"
    content = "<tr><td>OWNER</td><td>TABLE</td><td>NUMBER_ROWS</td><td>NEED_SPACE(M)</td><td>TRUE_SPACE(M)</td><td>RECOVERY(M)</td><td>RECOVER RATE</td></tr>"
    for item in data:
       content = content+"<tr><td>"+item[0]+"</td><td>"+str(item[1])+"</td><td>"+str(item[2])+"</td><td>"+str(item[3])+"</td><td>"+str(item[4])+"</td><td>"+str(item[5])+"</td><td>"+str(item[6])+"%</td></tr>"
    table_footer = "</table>"
    table = table_header+content+table_footer
    return table
